package msg;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import database.Database;

public class SysMsg {
	
	public static void sendMsgToChannelUsr(int adminId, int cid) {
		
		try {
			String title = closeInfo(cid);
			Date date = new Date(new java.util.Date().getTime());
			
			Connection con = Database.getConnection();
			Statement stmt = con.createStatement();

			ResultSet rs = stmt.executeQuery(String.format(
					"select distinct usr " +
					"from task " +
					"where channelThisType = %d or channelThatType = %d",
					cid, cid));
			
			PreparedStatement pstmt = con.prepareStatement(
					"insert into msg(" +
					"	from_id, to_id, title, content, dt, is_boardcast) " +
					"values(?, ?, ?, ?, ?, 0)");
			
			pstmt.setInt(1, adminId);
			pstmt.setString(3, title);
			pstmt.setString(4, "Your concerning tasks are deleted!");
			pstmt.setDate(5, date);

			while ( rs.next() ) {
				
				pstmt.setInt(2, rs.getInt("usr"));
				pstmt.executeUpdate();
			}
			
			stmt.close();
			con.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static String closeInfo(int cid) {
		
		try {
			StringBuffer title = new StringBuffer();
			
			Connection con = Database.getConnection();
			Statement stmt = con.createStatement();

			ResultSet rs = stmt.executeQuery(String.format(
					"select tag " +
					"from channel " +
					"where id = %d",
					cid));
			
			if ( rs.next() ) {
				title.append(rs.getString("tag"));
			}
			title.append(" is closed");
			
			stmt.close();
			con.close();
			
			return title.toString();
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
	}
}
